import hashlib
import itertools
import logging
import json
import time
from functools import reduce
from operator import or_

from flask import current_app as app, url_for, request_started
from flask_login import current_user, AnonymousUserMixin, UserMixin
from passlib.apps import custom_app_context as pwd_context
from sqlalchemy.exc import DBAPIError
from sqlalchemy.dialects import postgresql

from sqlalchemy_utils import EmailType
from sqlalchemy_utils.models import generic_repr

from redash import redis_connection
from redash.utils import generate_token, utcnow

from .base import db, Column, GFKBase, key_type, primary_key
from .mixins import TimestampMixin, BelongsToOrgMixin
from .types import json_cast_property, MutableDict, MutableList

logger = logging.getLogger(__name__)

@generic_repr("id", "strcustomercode", "strcustomername")
class CustomerTemp(db.Model):
    id = Column(db.Integer, primary_key=True)
    org_id = Column(key_type("Organization"), db.ForeignKey("organizations.id"))
    org = db.relationship("Organization", back_populates="customertemp")
    lngcustomerid =  Column(db.Integer, default=0)
    strcustomercode = Column(db.String(255))
    strcustomername = Column(db.String(255))
    lngcustomertypeid =  Column(db.Integer, default=0, nullable=True)
    strcustomertypecode =  Column(db.String(255), default=" ", nullable=True)
    strcustomertypename = Column(db.String(255), default=" ", nullable=True)
    blnisinactive =  Column(db.Boolean(True), default=True, nullable=True)
    bytstatus =  Column(db.Integer, default=0, nullable=True)
    blnchuli =  Column(db.Integer, default=0, nullable=True)
    representative =  Column(db.String(50), nullable=True)
    mobile =  Column(db.String(50), nullable=True)
    address =  Column(db.String(500), nullable=True)
    __tablename__ = "customertemp"
    __table_args__ = (db.Index("customertemp_org_code","org_id", "strcustomercode", unique=True),)

    #__table_args__ = {'extend_existing': True}
    def __str__(self):
        return "%s,%s" % (
            self.strcustomercode,
            self.strcustomername,
        )

    def to_dict(self):
        return {
            "id": self.id,
            "lngcustomerid": self.lngcustomerid,
            "strcustomercode": self.strcustomercode,
            "strcustomername": self.strcustomername,
            "lngcustomertypeid": self.lngcustomertypeid,
            "strcustomertypecode": self.strcustomertypecode,
            "strcustomertypename": self.strcustomertypename,
            "representative": self.representative,
            "mobile": self.mobile,
            "address": self.address,
            "blnisinactive": self.blnisinactive,
            "bytstatus": self.bytstatus,
            "blnchuli": self.blnchuli,
        }
    @classmethod
    def get_by_id(cls, id):
        return cls.query.filter(cls.id == id).one()

    @classmethod
    def get_id(cls):
        strsql="""select array_to_json(array_agg(row_to_json(t))) from (select nextval('customertemp_id_seq'::regclass) as id) t """
        return db.session.execute(strsql).fetchone()

    @classmethod
    def get_by_customertempid(cls, customertempid):
        strsql="""select * From contracts where party_aid=""" +customertempid +"""  limit 5 """
        return db.session.execute(strsql)

    @classmethod
    def get_by_code(cls, code,org):
        return cls.query.filter(cls.org == org,cls.strcustomercode == code).one()

    @classmethod
    def get_by_status(cls, bytstatus,org):
        return cls.query.filter(cls.org == org,cls.bytstatus == bytstatus,cls.blnisinactive == True)

###########################20200502HRP职员表#####################################
@generic_repr("id", "stremployeecode", "stremployeename")
class EmployeeTemp(db.Model):
    id = Column(db.Integer, primary_key=True)
    lngemployeeid =  Column(db.Integer, default=0, nullable=True)
    org_id = Column(key_type("Organization"), db.ForeignKey("organizations.id"))
    org = db.relationship("Organization", back_populates="employeetemp")
    stremployeecode = Column(db.String(255))
    stremployeename = Column(db.String(255))
    blnisinactive =  Column(db.Integer, default=0, nullable=True)
    bytstatus =  Column(db.Integer, default=0, nullable=True)
    blnchuli =  Column(db.Integer, default=0, nullable=True)
    strlastdate =  Column(db.DateTime(True), nullable=True)

    __tablename__ = "employeetemp"
    __table_args__ = (db.Index("employeetemp_org_code", "org_id", "stremployeecode", unique=True),)

    def __str__(self):
        return "%s,%s" % (
            self.stremployeecode,
            self.stremployeename,
        )

    def to_dict(self):
        return {
            "id": self.id,
            "lngemployeeid": self.lngemployeeid,
            "stremployeecode": self.stremployeecode,
            "stremployeename": self.stremployeename,
            "blnisinactive": self.blnisinactive,
            "bytstatus": self.bytstatus,
            "blnchuli": self.blnchuli,
            "strlastdate": self.strlastdate,
        }

    @classmethod
    def get_by_code(cls, code,org):
        return cls.query.filter(cls.org == org,cls.stremployeecode == code).one()

    @classmethod
    def get_by_status(cls, bytstatus,org):
        return cls.query.filter(cls.org == org,cls.bytstatus == bytstatus,cls.blnisinactive == 0)
################################################################
####################合同申请###########################################
@generic_repr("id", "code", "name")
class ContractApply(TimestampMixin, BelongsToOrgMixin, db.Model):
    id = Column(db.Integer, primary_key=True)
    org_id = Column(key_type("Organization"), db.ForeignKey("organizations.id"))
    org = db.relationship("Organization", back_populates="contractapply")
    userid = Column(db.Integer) #--操作员ID
    bill_date = Column(db.Date) #申请日期
    employeeid = Column(db.Integer) #--经办人
    departmentid = Column(db.Integer) #--经办部门
    code = Column(db.String(100)) #合同编号
    name = Column(db.String(255)) #合同名称
    # type = Column(db.String(255))  # 合同类别
    type = Column(
        MutableList.as_mutable(postgresql.ARRAY(db.String(255))), nullable=True
    ) #附件
    party_aid = Column(db.Integer) #--甲方ID
    party_bid = Column(db.Integer, default=0) #--乙方
    party_cid = Column(db.Integer, default=0) #--其它关联方
    pay_direction = Column(db.Integer, default=0) #--收付方向
    pricetype =Column(db.Integer, default=0) #----价格类型 0固定价,1单价结算
    sign_date = Column(db.Date, nullable=True) #合同签订日期
    start_date = Column(db.Date, nullable=True) #合同开始日期
    end_date = Column(db.Date, nullable=True) #合同终止日期
    amount = Column(db.Numeric) #金额
    amount_words = Column(db.String(255))  # 合同金额(大写)
    status =  Column(db.Integer, default=0, nullable=True) #--状态
    # annex = Column(
    #     MutableList.as_mutable(postgresql.ARRAY(db.String(255))), nullable=True
    # ) #附件
    # annex = Column(
    #     MutableList.as_mutable(postgresql.JSON),
    #     nullable=True,
    #     server_default="{}",
    #     default={},
    # ) #附件
    message = Column(db.Text, default=None, nullable=True)  #  纪录事项

    __tablename__ = "contractapply"
    __table_args__ = (db.Index("contractapply_org_code", "org_id", "code", unique=True),)

    def __str__(self):
        return "%s,%s" % (
            self.code,
            self.name,
        )

    def to_dict(self):
        return {
            "id": self.id,
            "userid": self.userid,
            "bill_date": self.bill_date,
            "employeeid": self.employeeid,
            "departmentid": self.departmentid,
            "code": self.code,
            "name": self.name,
            "type": self.type,
            "party_aid": self.party_aid,
            "party_bid": self.party_bid,
            "party_cid": self.party_cid,
            "pay_direction": self.pay_direction,
            "pricetype": self.pricetype,
            "sign_date": self.sign_date,
            "start_date": self.start_date,
            "end_date": self.end_date,
            "amount": self.amount,
            "amount_words": self.amount_words,
            "status": self.status,
            # "annex": self.annex,
            "message": self.message,
            "updated_at": self.updated_at,
            "created_at": self.created_at,
        }

    @classmethod
    def get_by_id(cls, id):
        return cls.query.filter(cls.id == id).one()

    @classmethod
    def get_id(cls):
        strsql="""select array_to_json(array_agg(row_to_json(t))) from (select nextval('contractapply_id_seq'::regclass) as id) t """
        return db.session.execute(strsql).fetchone()

    @classmethod
    def get_code(cls,_tblname,_type,_tblfield,_bill_date,_org_id,_userid):
        strsql="""select array_to_json(array_agg(row_to_json(t))) from (select public.gaincode(:tblname,:type,:tblfield,:bill_date,:org_id,:userid) as code) t """
        return db.session.execute(strsql,{"tblname": _tblname,"type": _type,"tblfield": _tblfield,"bill_date": _bill_date,"org_id": _org_id,"userid":_userid}).fetchone()

    @classmethod
    def get_pagequery(cls,_tblname,_strgetfield,_orderfield,_pagesize,_pageindex,_ordertype,_strwhere):
        strsql="""select public.overpage(:tblname,:strgetfield,:orderfield,:pagesize,:pageindex,:ordertype,:strwhere)"""
        return db.session.execute(strsql,{"tblname": _tblname,"strgetfield": _strgetfield,"orderfield": _orderfield,"pagesize": _pagesize
                                          ,"pageindex": _pageindex,"ordertype": _ordertype,"strwhere": _strwhere}).fetchone()
   #生成下游单据
    @classmethod
    def get_pushbill(cls,_sourcebill,_sourcebillid,_bill,_userid,_typeid):
        strsql="""select public.pushbill(:sourcebill,:sourcebillid,:bill,:userid,:typeid)"""
        return db.session.execute(strsql,{"sourcebill": _sourcebill,"sourcebillid": _sourcebillid
                                          ,"bill": _bill,"userid": _userid,"typeid": _typeid}).fetchone()
################合同20200517###############################################
@generic_repr("id", "code", "name")
class Contracts(TimestampMixin, BelongsToOrgMixin, db.Model):
    id = Column(db.Integer, primary_key=True)
    org_id = Column(key_type("Organization"), db.ForeignKey("organizations.id"))
    org = db.relationship("Organization", back_populates="contracts")
    userid = Column(db.Integer) #--操作员ID
    bill_date = Column(db.Date) #日期
    employeeid = Column(db.Integer) #--经办人
    departmentid = Column(db.Integer) #--经办部门
    code = Column(db.String(100)) #合同编号
    name = Column(db.String(255)) #合同名称
    # type = Column(db.String(255))  # 合同类别
    type = Column(
        MutableList.as_mutable(postgresql.ARRAY(db.String(255))), nullable=True
    ) #合同类别
    contracttype = Column(
        MutableList.as_mutable(postgresql.ARRAY(db.String(255))), nullable=True
    ) #合同类型
    party_aid = Column(db.Integer) #--甲方ID
    party_bid = Column(db.Integer, default=0) #--乙方
    party_cid = Column(db.Integer, default=0) #--其它关联方
    pay_direction = Column(db.Integer, default=0) #--收付方向
    pricetype = Column(db.Integer, default=0)
    sign_date = Column(db.Date, nullable=True) #合同签订日期
    start_date = Column(db.Date, nullable=True) #合同开始日期
    end_date = Column(db.Date, nullable=True) #合同终止日期
    warn_date= Column(db.Date, nullable=True) #提醒日期
    warn_message= Column(db.Text, default=None, nullable=True) #提醒内容
    warn_check= Column(db.Text, default=None, nullable=True) #审核提醒
    warn_checkdate= Column(db.Date, nullable=True) #截止审批日期
    volumepurchase = Column(db.Integer, default=0, nullable=True) #--带量采购
    amount = Column(db.Numeric) #金额
    amount_words = Column(db.String(255), default=None, nullable=True)  # 合同金额(大写)
    status =  Column(db.Integer, default=0, nullable=True) #--状态
    budgetcode= Column(db.String(255), default=None, nullable=True) #预算编号
    officialcode= Column(db.String(255), default=None, nullable=True) #正式合同编号
    templateid= Column(db.Integer, default=0, nullable=True) #模板编号
    templatecontent= Column(db.Text, default=None, nullable=True) #模板内容
    typeid = Column(db.Integer, default=0, nullable=True)
    recordcode = Column(db.String(255), default=None, nullable=True) #备案号
    puremployeeid=Column(db.Integer, default=0, nullable=True)#采购经办人
    # annex = Column(
    #     MutableList.as_mutable(postgresql.ARRAY(db.String(255))), nullable=True
    # ) #附件
    # annex = Column(
    #     MutableList.as_mutable(postgresql.JSON),
    #     nullable=True,
    #     server_default="{}",
    #     default={},
    # ) #附件
    contractsealid= Column(db.Integer, default=0, nullable=True) #---合同用印申请单ID
    contractrecordid= Column(db.Integer, default=0, nullable=True) #-
    billstatusid= Column(db.Integer, default=0, nullable=True) #-   执行中, 1    中止, 2    验收, 3    终止
    message = Column(db.Text, default=None, nullable=True)  #  纪录事项
    #amounttype = Column(db.String(255))  # 资金类别
    amounttype = Column(
        MutableList.as_mutable(postgresql.ARRAY(db.String(255))), nullable=True
    ) #合同类别
    infrastructure = Column(db.Integer, default=0, nullable=True)#是否第三方
    sourcetype = Column(db.String(255), default=None, nullable=True)  # 来源单据类型
    sourceid = Column(db.Integer, default=0, nullable=True) #--来源单据ID
    zhengcai = Column(db.Integer, default=0, nullable=True)#是否政采
    isbulu = Column(db.Integer, default=0, nullable=True)#是否补录
    iskeyan = Column(db.Integer, default=0, nullable=True)#是否科研
    iszhaobiao= Column(db.Integer, default=0, nullable=True) #--是否招标
    empowerperson = Column(db.String(255), default=None, nullable=True) #授权人
    empowersex = Column(db.String(10), default=None, nullable=True) #性别
    empowerduties = Column(db.String(255), default=None, nullable=True) #职务
    agreementcount = Column(db.String(10), default=None, nullable=True) #协议份数
    warnsealuser = Column(db.String(10), default=None, nullable=True) #用印提醒人

    __tablename__ = "contracts"
    __table_args__ = (db.Index("contracts_org_code", "org_id", "code", unique=True),)

    def __str__(self):
        return "%s,%s" % (
            self.code,
            self.name,
        )

    def to_dict(self):
        return {
            "id": self.id,
            "userid": self.userid,
            "bill_date": self.bill_date,
            "employeeid": self.employeeid,
            "departmentid": self.departmentid,
            "code": self.code,
            "name": self.name,
            "type": self.type,
            "contracttype": self.contracttype,
            "party_aid": self.party_aid,
            "party_bid": self.party_bid,
            "party_cid": self.party_cid,
            "pay_direction": self.pay_direction,
            "pricetype": self.pricetype,
            "sign_date": self.sign_date,
            "start_date": self.start_date,
            "end_date": self.end_date,
            "warn_date": self.warn_date,
            "warn_message": self.warn_message,
            "warn_check": self.warn_check,
            "warn_checkdate": self.warn_checkdate,
            "volumepurchase": self.volumepurchase,
            "amount": self.amount,
            "amount_words": self.amount_words,
            "status": self.status,
            "budgetcode": self.budgetcode,
            "officialcode": self.officialcode,
            "templateid": self.templateid,
            "templatecontent": self.templatecontent,
            # "annex": self.annex,
            "message": self.message,
            "updated_at": self.updated_at,
            "created_at": self.created_at,
            "sourcetype": self.sourcetype,
            "sourceid": self.sourceid,
            "contractsealid": self.contractsealid,
            "contractrecordid": self.contractrecordid,
            "billstatusid": self.billstatusid,
            "typeid": self.typeid,
            "infrastructure": self.infrastructure,
            "amounttype": self.amounttype,
            "recordcode": self.recordcode,
            "puremployeeid":self.puremployeeid,
            "zhengcai":self.zhengcai,
            "isbulu":self.isbulu,
            "iskeyan":self.iskeyan,
            "iszhaobiao":self.iszhaobiao,
            "empowerperson":self.empowerperson,
            "empowersex":self.empowersex,
            "empowerduties":self.empowerduties,
            "agreementcount":self.agreementcount,
            "warnsealuser":self.warnsealuser,
        }

    @classmethod
    def get_by_id(cls, id):
        return cls.query.filter(cls.id == id).one()

    @classmethod
    def get_by_templateid(cls, templateid):
        strsql="""select * From contracts where templateid=""" +templateid +"""  limit 5 """
        return db.session.execute(strsql)

    @classmethod
    def getactivity_by_id(cls,id,sourcetype):
        strsql="""select array_to_json(array_agg(row_to_json(t))) from (select t1.id ,t1.userid , t1.contractsid, COALESCE(t3.name,'') || ' ' || COALESCE(t4.name,'') || ' ' || t1.message  as message,
            to_char(t1.updated_at, 'YYYY-MM-DD') as updated_at,
            case when (select COALESCE(value,'0') From public.globalsetting where type='合同主页面' and code='时间线显示日期')='1' then to_char(t1.created_at, 'YYYY-MM-DD') else '' end as created_at ,
            t1.sourcetype,t1.sourceid,t3.name,t4.name
            from public.contractactivity t1 left join public."""+sourcetype+""" t2 on t1.contractsid=t2.id 
            left join public.users t4 on t1.userid=t4.id left join public.orgunit t3 on t4.orgunit_id=t3.id
         where t1.contractsid="""+str(id)
        if sourcetype=='contracts' :
            strsql=strsql+""" and t1.sourceid>0 """
        else :
            strsql=strsql+""" and t1.sourceid=0 and t1.sourcetype='"""+sourcetype+"""' """
        strsql=strsql+"""  order by t1.updated_at desc) t """
        return db.session.execute(strsql).fetchone()

    @classmethod
    def get_id(cls):
        strsql="""select array_to_json(array_agg(row_to_json(t))) from (select nextval('contracts_id_seq'::regclass) as id) t """
        return db.session.execute(strsql).fetchone()
    # 20200718查询未选择的合同
    @classmethod
    def get_selcontracts(cls,strsql):
        return db.session.execute(strsql).fetchone()

################合同用印申请单20200520###############################################
@generic_repr("id", "code", "name")
class ContractSeal(TimestampMixin, BelongsToOrgMixin, db.Model):
    id = Column(db.Integer, primary_key=True)
    org_id = Column(key_type("Organization"), db.ForeignKey("organizations.id"))
    org = db.relationship("Organization", back_populates="contractseal")
    userid = Column(db.Integer) #--操作员ID
    bill_date = Column(db.Date) #日期
    contractsid = Column(db.Integer) #--合同ID
    code = Column(db.String(100)) #编号
    name = Column(db.String(255)) #名称
    departmentid =  Column(db.Integer, default=0)
    status =  Column(db.Integer, default=0, nullable=True) #--状态
    message = Column(db.Text, default=None, nullable=True)  #  纪录事项
    sourcetype = Column(db.String(255), default=None, nullable=True)  # 来源单据类型
    sourceid = Column(db.Integer, default=0, nullable=True) #--来源单据ID
    employeeid = Column(db.Integer, default=0, nullable=True) #--院长办公室
    sealdate = Column(db.Date, nullable=True) #盖章时间
    sealcount =  Column(db.Integer, default=0, nullable=True) #--盖章几份
    handledept = Column(db.Integer, default=0, nullable=True) #--经办人员
    archive = Column(db.Boolean(True), default=True, nullable=True)#---是否归档
    __tablename__ = "contractseal"
    __table_args__ = (db.Index("contractseal_org_code","org_id", "code", unique=True),)

    def __str__(self):
        return "%s,%s" % (
            self.code,
            self.name,
        )

    def to_dict(self):
        return {
            "id": self.id,
            "userid": self.userid,
            "bill_date": self.bill_date,
            "contractsid": self.contractsid,
            "code": self.code,
            "name": self.name,
            "departmentid":self.departmentid,
            "status": self.status,
            "message": self.message,
            "updated_at": self.updated_at,
            "created_at": self.created_at,
            "sourcetype": self.sourcetype,
            "sourceid": self.sourceid,
            "employeeid": self.employeeid,
            "sealdate": self.sealdate,
            "sealcount": self.sealcount,
            "handledept": self.handledept,
            "archive": self.archive,
        }

    @classmethod
    def get_by_id(cls, id):
        return cls.query.filter(cls.id == id).one()

    @classmethod
    def get_id(cls):
        strsql="""select array_to_json(array_agg(row_to_json(t))) from (select nextval('contractseal_id_seq'::regclass) as id) t """
        return db.session.execute(strsql).fetchone()
################合同备案申请单20200520###############################################
@generic_repr("id", "code", "name")
class ContractRecord(TimestampMixin, BelongsToOrgMixin, db.Model):
    id = Column(db.Integer, primary_key=True)
    org_id = Column(key_type("Organization"), db.ForeignKey("organizations.id"))
    org = db.relationship("Organization", back_populates="contractrecord")
    userid = Column(db.Integer) #--操作员ID
    bill_date = Column(db.Date) #日期
    contractsid = Column(db.Integer) #--合同ID
    code = Column(db.String(100)) #编号
    name = Column(db.String(255)) #名称
    departmentid =  Column(db.Integer, default=0)
    status =  Column(db.Integer, default=0, nullable=True) #--状态
    message = Column(db.Text, default=None, nullable=True)  #  纪录事项
    sourcetype = Column(db.String(255), default=None, nullable=True)  # 来源单据类型
    sourceid = Column(db.Integer, default=0, nullable=True) #--来源单据ID

    __tablename__ = "contractrecord"
    __table_args__ = (db.Index("contractrecord_org_code","org_id", "code", unique=True),)

    def __str__(self):
        return "%s,%s" % (
            self.code,
            self.name,
        )

    def to_dict(self):
        return {
            "id": self.id,
            "userid": self.userid,
            "bill_date": self.bill_date,
            "contractsid": self.contractsid,
            "code": self.code,
            "name": self.name,
            "departmentid":self.departmentid,
            "status": self.status,
            "message": self.message,
            "updated_at": self.updated_at,
            "created_at": self.created_at,
            "sourcetype": self.sourcetype,
            "sourceid": self.sourceid,
        }

    @classmethod
    def get_by_id(cls, id):
        return cls.query.filter(cls.id == id).one()

    @classmethod
    def get_id(cls):
        strsql="""select array_to_json(array_agg(row_to_json(t))) from (select nextval('contractrecord_id_seq'::regclass) as id) t """
        return db.session.execute(strsql).fetchone()

################合同变更申请单20200522###############################################
@generic_repr("id", "code", "name")
class ContractChange(TimestampMixin, BelongsToOrgMixin, db.Model):
    id = Column(db.Integer, primary_key=True)
    org_id = Column(key_type("Organization"), db.ForeignKey("organizations.id"))
    org = db.relationship("Organization", back_populates="contractchange")
    userid = Column(db.Integer) #--操作员ID
    bill_date = Column(db.Date) #日期
    contractsid = Column(db.Integer) #--合同ID
    employeeid = Column(db.Integer) #--经办人
    departmentid = Column(db.Integer) #--经办部门
    code = Column(db.String(100)) #编号
    contractcode= Column(db.String(100)) #合同编号
    name = Column(db.String(255)) #合同名称
    # type = Column(db.String(255))  # 合同类别
    type = Column(
        MutableList.as_mutable(postgresql.ARRAY(db.String(255))), nullable=True
    ) #附件
    party_aid = Column(db.Integer) #--甲方ID
    party_bid = Column(db.Integer, default=0) #--乙方
    party_cid = Column(db.Integer, default=0) #--其它关联方
    pay_direction = Column(db.Integer, default=0) #--收付方向
    pricetype = Column(db.Integer, default=0)
    sign_date = Column(db.Date, nullable=True) #合同签订日期
    start_date = Column(db.Date, nullable=True) #合同开始日期
    end_date = Column(db.Date, nullable=True) #合同终止日期
    amount = Column(db.Numeric) #金额
    amount_words = Column(db.String(255))  # 合同金额(大写)
    status =  Column(db.Integer, default=0, nullable=True) #--状态
    budgetcode= Column(db.String(255), default=None, nullable=True) #预算编号
    officialcode= Column(db.String(255), default=None, nullable=True) #正式合同编号
    templateid= Column(db.Integer, default=0, nullable=True) #模板编号
    templatecontent= Column(db.Text, default=None, nullable=True) #模板内容
    changebefore = Column(db.Text, default=None, nullable=True) 
    # Column(
    #     MutableList.as_mutable(postgresql.JSON),
    #     nullable=True,
    #     server_default="{}",
    #     default={},
    # ) #附件
    message = Column(db.Text, default=None, nullable=True)  #  纪录事项
    sourcetype = Column(db.String(255), default=None, nullable=True)  # 来源单据类型
    sourceid = Column(db.Integer, default=0, nullable=True) #--来源单据ID
    contractsealid = Column(db.Integer, default=0, nullable=True) 
    contractrecordid = Column(db.Integer, default=0, nullable=True) 
    billstatusid = Column(db.Integer, default=0, nullable=True) 
    typeid = Column(db.Integer, default=0, nullable=True) 
    workflow_state = Column(db.Integer, default=0, nullable=True) 
    amounttype = Column(
        MutableList.as_mutable(postgresql.ARRAY(db.String(255))), nullable=True
    ) 
    infrastructure = Column(db.Integer, default=0, nullable=True) 
    recordcode = Column(db.String(255), default=None, nullable=True) 
    puremployeeid = Column(db.Integer, default=0, nullable=True) 
    warn_date = Column(db.Date, nullable=True)
    contracttype = Column(
        MutableList.as_mutable(postgresql.ARRAY(db.String(255))), nullable=True
    ) 
    zhengcai = Column(db.Integer, default=0, nullable=True) 
    isbulu = Column(db.Integer, default=0, nullable=True) 
    warn_message = Column(db.Text, default=None, nullable=True) 
    warn_check = Column(db.Text, default=None, nullable=True) 
    volumepurchase = Column(db.Integer, default=0, nullable=True) 
    warn_checkdate = Column(db.Date, nullable=True)
    iskeyan = Column(db.Integer, default=0, nullable=True) 
    iszhaobiao = Column(db.Integer, default=0, nullable=True) 
    empowersex = Column(db.String(10), default=None, nullable=True) 
    empowerduties = Column(db.String(255), default=None, nullable=True) 
    agreementcount = Column(db.String(10), default=None, nullable=True) 
    empowerperson = Column(db.String(255), default=None, nullable=True) 
    warnsealuser = Column(db.String(255), default=None, nullable=True) 
    __tablename__ = "contractchange"
    __table_args__ = (db.Index("contractchange_org_code","org_id", "code", unique=True),)

    def __str__(self):
        return "%s,%s" % (
            self.code,
            self.name,
        )

    def to_dict(self):
        return {
            "id": self.id,
            "userid": self.userid,
            "bill_date": self.bill_date,
            "contractsid": self.contractsid,
            "employeeid": self.employeeid,
            "departmentid": self.departmentid,
            "code": self.code,
            "contractcode": self.contractcode,
            "name": self.name,
            "type": self.type,
            "party_aid": self.party_aid,
            "party_bid": self.party_bid,
            "party_cid": self.party_cid,
            "pay_direction": self.pay_direction,
            "pricetype": self.pricetype,
            "sign_date": self.sign_date,
            "start_date": self.start_date,
            "end_date": self.end_date,
            "amount": self.amount,
            "amount_words": self.amount_words,
            "status": self.status,
            "budgetcode": self.budgetcode,
            "officialcode": self.officialcode,
            "templateid": self.templateid,
            "templatecontent": self.templatecontent,
            # "annex": self.annex,
            "changebefore": self.changebefore,
            "message": self.message,
            "updated_at": self.updated_at,
            "created_at": self.created_at,
            "sourcetype": self.sourcetype,
            "sourceid": self.sourceid,
            "contractsealid": self.contractsealid,
            "contractrecordid": self.contractrecordid ,
            "billstatusid": self.billstatusid ,
            "typeid": self.typeid ,
            "workflow_state": self.workflow_state ,
            "amounttype": self.amounttype ,
            "infrastructure": self.infrastructure ,
            "recordcode": self.recordcode ,
            "puremployeeid": self.puremployeeid ,
            "warn_date": self.warn_date ,
            "contracttype": self.contracttype ,
            "zhengcai": self.zhengcai ,
            "isbulu": self.isbulu ,
            "warn_message": self.warn_message ,
            "warn_check": self.warn_check ,
            "volumepurchase": self.volumepurchase ,
            "warn_checkdate": self.warn_checkdate ,
            "iskeyan": self.iskeyan ,
            "iszhaobiao": self.iszhaobiao ,
            "empowersex": self.empowersex ,
            "empowerduties": self.empowerduties ,
            "agreementcount": self.agreementcount ,
            "empowerperson": self.empowerperson ,
            "warnsealuser": self.warnsealuser ,
        }

    @classmethod
    def get_by_id(cls, id):
        return cls.query.filter(cls.id == id).one()

    @classmethod
    def get_id(cls):
        strsql="""select array_to_json(array_agg(row_to_json(t))) from (select nextval('contractchange_id_seq'::regclass) as id) t """
        return db.session.execute(strsql).fetchone()

################合同违约登记单20200522###############################################
@generic_repr("id", "code", "name")
class ContractProgress(TimestampMixin, BelongsToOrgMixin, db.Model):
    id = Column(db.Integer, primary_key=True)
    org_id = Column(key_type("Organization"), db.ForeignKey("organizations.id"))
    org = db.relationship("Organization", back_populates="contractprogress")
    userid = Column(db.Integer) #--操作员ID
    bill_date = Column(db.Date) #日期
    contractsid = Column(db.Integer) #--合同ID
    percent = Column(db.Integer) #--完成比例%
    code = Column(db.String(100)) #编号
    name = Column(db.String(255)) #名称
    departmentid = Column(db.Integer, default=0) #--经办部门
    status =  Column(db.Integer, default=0, nullable=True) #--状态
    message = Column(db.Text, default=None, nullable=True)  #  纪录事项
    sourcetype = Column(db.String(255), default=None, nullable=True)  # 来源单据类型
    sourceid = Column(db.Integer, default=0, nullable=True) #--来源单据ID

    __tablename__ = "contractprogress"
    __table_args__ = (db.Index("contractprogress_org_code","org_id", "code", unique=True),)

    def __str__(self):
        return "%s,%s" % (
            self.code,
            self.name,
        )

    def to_dict(self):
        return {
            "id": self.id,
            "userid": self.userid,
            "bill_date": self.bill_date,
            "contractsid": self.contractsid,
            "percent": self.percent,
            "code": self.code,
            "name": self.name,
            "departmentid": self.departmentid,
            "status": self.status,
            "message": self.message,
            "updated_at": self.updated_at,
            "created_at": self.created_at,
            "sourcetype": self.sourcetype,
            "sourceid": self.sourceid,
        }

    @classmethod
    def get_by_id(cls, id):
        return cls.query.filter(cls.id == id).one()

    @classmethod
    def get_id(cls):
        strsql="""select array_to_json(array_agg(row_to_json(t))) from (select nextval('contractprogress_id_seq'::regclass) as id) t """
        return db.session.execute(strsql).fetchone()

################合同付款单20200522###############################################
@generic_repr("id", "code", "name")
class ContractSettle(TimestampMixin, BelongsToOrgMixin, db.Model):
    id = Column(db.Integer, primary_key=True)
    org_id = Column(key_type("Organization"), db.ForeignKey("organizations.id"))
    org = db.relationship("Organization", back_populates="contractsettle")
    userid = Column(db.Integer) #--操作员ID
    bill_date = Column(db.Date) #日期
    contractsid = Column(db.Integer) #--合同ID
    contractprogressid = Column(db.Integer, default=0, nullable=True) #--合同进度ID
    percent = Column(db.Integer, default=0, nullable=True) #--完成比例%
    code = Column(db.String(100)) #编号
    name = Column(db.String(255)) #名称
    departmentid = Column(db.Integer, default=0) #--经办部门
    status =  Column(db.Integer, default=0, nullable=True) #--状态
    message = Column(db.Text, default=None, nullable=True)  #  纪录事项
    sourcetype = Column(db.String(255), default=None, nullable=True)  # 来源单据类型
    sourceid = Column(db.Integer, default=0, nullable=True) #--来源单据ID

    __tablename__ = "contractsettle"
    __table_args__ = (db.Index("contractsettle_org_code","org_id", "code", unique=True),)

    def __str__(self):
        return "%s,%s" % (
            self.code,
            self.name,
        )

    def to_dict(self):
        return {
            "id": self.id,
            "userid": self.userid,
            "bill_date": self.bill_date,
            "contractsid": self.contractsid,
            "contractprogressid": self.contractprogressid,
            "percent": self.percent,
            "code": self.code,
            "name": self.name,
            "departmentid": self.departmentid,
            "status": self.status,
            "message": self.message,
            "updated_at": self.updated_at,
            "created_at": self.created_at,
            "sourcetype": self.sourcetype,
            "sourceid": self.sourceid,
        }

    @classmethod
    def get_by_id(cls, id):
        return cls.query.filter(cls.id == id).one()

    @classmethod
    def get_id(cls):
        strsql="""select array_to_json(array_agg(row_to_json(t))) from (select nextval('contractsettle_id_seq'::regclass) as id) t """
        return db.session.execute(strsql).fetchone()
################合同终止单20200524###############################################
@generic_repr("id", "code", "name")
class ContractStop(TimestampMixin, BelongsToOrgMixin, db.Model):
    id = Column(db.Integer, primary_key=True)
    org_id = Column(key_type("Organization"), db.ForeignKey("organizations.id"))
    org = db.relationship("Organization", back_populates="contractstop")
    userid = Column(db.Integer) #--操作员ID
    bill_date = Column(db.Date) #日期
    contractsid = Column(db.Integer) #--合同ID
    code = Column(db.String(100)) #编号
    name = Column(db.String(255)) #名称
    departmentid = Column(db.Integer, default=0) #--经办部门
    status =  Column(db.Integer, default=0, nullable=True) #--状态
    message = Column(db.Text, default=None, nullable=True)  #  纪录事项
    sourcetype = Column(db.String(255), default=None, nullable=True)  # 来源单据类型
    sourceid = Column(db.Integer, default=0, nullable=True) #--来源单据ID

    __tablename__ = "contractstop"
    __table_args__ = (db.Index("contractstop_org_code","org_id", "code", unique=True),)

    def __str__(self):
        return "%s,%s" % (
            self.code,
            self.name,
        )

    def to_dict(self):
        return {
            "id": self.id,
            "userid": self.userid,
            "bill_date": self.bill_date,
            "contractsid": self.contractsid,
            "code": self.code,
            "name": self.name,
            "departmentid": self.departmentid,
            "status": self.status,
            "message": self.message,
            "updated_at": self.updated_at,
            "created_at": self.created_at,
            "sourcetype": self.sourcetype,
            "sourceid": self.sourceid,
        }

    @classmethod
    def get_by_id(cls, id):
        return cls.query.filter(cls.id == id).one()

    @classmethod
    def get_id(cls):
        strsql="""select array_to_json(array_agg(row_to_json(t))) from (select nextval('contractstop_id_seq'::regclass) as id) t """
        return db.session.execute(strsql).fetchone()
################合同验收单20200524###############################################
@generic_repr("id", "code", "name")
class ContractFinish(TimestampMixin, BelongsToOrgMixin, db.Model):
    id = Column(db.Integer, primary_key=True)
    org_id = Column(key_type("Organization"), db.ForeignKey("organizations.id"))
    org = db.relationship("Organization", back_populates="contractfinish")
    userid = Column(db.Integer) #--操作员ID
    bill_date = Column(db.Date) #日期
    contractsid = Column(db.Integer) #--合同ID
    code = Column(db.String(100)) #编号
    name = Column(db.String(255)) #名称
    departmentid = Column(db.Integer, default=0) #--经办部门
    status =  Column(db.Integer, default=0, nullable=True) #--状态
    message = Column(db.Text, default=None, nullable=True)  #  纪录事项
    sourcetype = Column(db.String(255), default=None, nullable=True)  # 来源单据类型
    sourceid = Column(db.Integer, default=0, nullable=True) #--来源单据ID

    __tablename__ = "contractfinish"
    __table_args__ = (db.Index("contractfinish_org_code","org_id", "code", unique=True),)

    def __str__(self):
        return "%s,%s" % (
            self.code,
            self.name,
        )

    def to_dict(self):
        return {
            "id": self.id,
            "userid": self.userid,
            "bill_date": self.bill_date,
            "contractsid": self.contractsid,
            "code": self.code,
            "name": self.name,
            "departmentid": self.departmentid,
            "status": self.status,
            "message": self.message,
            "updated_at": self.updated_at,
            "created_at": self.created_at,
            "sourcetype": self.sourcetype,
            "sourceid": self.sourceid,
        }

    @classmethod
    def get_by_id(cls, id):
        return cls.query.filter(cls.id == id).one()

    @classmethod
    def get_id(cls):
        strsql="""select array_to_json(array_agg(row_to_json(t))) from (select nextval('contractfinish_id_seq'::regclass) as id) t """
        return db.session.execute(strsql).fetchone()
################合同款项20200530###############################################
@generic_repr("id")
class ContractAmount(TimestampMixin, BelongsToOrgMixin, db.Model):
    id = Column(db.Integer, primary_key=True)
    userid = Column(db.Integer) #--操作员ID
    bill_date = Column(db.Date) #日期
    amount_enum = Column(db.String(255))
    amount = Column(db.Numeric, default=0, nullable=True) #金额
    contractsid = Column(db.Integer) #--合同ID
    message = Column(db.Text, default=None, nullable=True)  #  纪录事项
    warn_date = Column(db.Date)  #提醒日期
    warn_message = Column(db.Text, default=None, nullable=True)  #  纪录事项
    sourcetype = Column(db.String(255), default=None, nullable=True)  # 来源单据类型
    sourceid = Column(db.Integer, default=0, nullable=True) #--来源单据ID

    __tablename__ = "contractamount"
    #__table_args__ = (db.Index("id",  unique=True),)

    def __str__(self):
        return "%s,%s" % (
            self.id,
            self.message,
        )

    def to_dict(self):
        return {
            "id": self.id,
            "userid": self.userid,
            "bill_date": self.bill_date,
            "contractsid": self.contractsid,
            "amount_enum": self.amount_enum,
            "amount": self.amount,
            "message": self.message,
            "warn_date": self.warn_date,
            "warn_message": self.warn_message,
            "updated_at": self.updated_at,
            "created_at": self.created_at,
            "sourcetype": self.sourcetype,
            "sourceid": self.sourceid,
        }

    @classmethod
    def get_by_id(cls, id):
        return cls.query.filter(cls.id == id).one()
################附件20200613###############################################
@generic_repr("id")
class Attaches(TimestampMixin, BelongsToOrgMixin, db.Model):
    id = Column(db.Integer, primary_key=True)
    org_id = Column(key_type("Organization"), db.ForeignKey("organizations.id"))
    org = db.relationship("Organization", back_populates="attaches")
    userid = Column(db.Integer) #--操作员ID
    type = Column(db.String(255)) #类型
    annex = Column(
        MutableList.as_mutable(postgresql.JSON),
        nullable=True,
        server_default="{}",
        default={},
    ) #附件
    status = Column(db.Integer, default=0, nullable=True) #--状态
    sourcetype = Column(db.String(255))  # 来源单据类型
    sourceid = Column(db.Integer, default=0) #--来源单据ID

    __tablename__ = "attaches"
    __table_args__ = (db.Index("attaches_org_sourcetype_sourceid","org_id","type","sourcetype","sourceid",  unique=True),)

    def __str__(self):
        return "%s,%s" % (
            self.id,
            self.sourceid,
        )

    def to_dict(self):
        return {
            "id": self.id,
            "userid": self.userid,
            "type": self.type,
            "annex": self.annex,
            "status": self.status,
            "updated_at": self.updated_at,
            "created_at": self.created_at,
            "sourcetype": self.sourcetype,
            "sourceid": self.sourceid,
        }

    @classmethod
    def get_by_id(cls, sourceid,type):
        return cls.query.filter(cls.sourceid == sourceid, cls.type == type).one()
################合同活动列表20200613###############################################
@generic_repr("id")
class ContractActivity(TimestampMixin, BelongsToOrgMixin, db.Model):
    id = Column(db.Integer, primary_key=True)
    userid = Column(db.Integer) #--操作员ID
    contractsid = Column(db.Integer) #--合同ID
    message = Column(db.Text, default=None, nullable=True)  #  纪录事项
    sourcetype = Column(db.String(255))  # 来源单据类型
    sourceid = Column(db.Integer) #--来源单据ID

    __tablename__ = "contractactivity"
    # __table_args__ = (db.Index("id",  unique=True),)

    def __str__(self):
        return "%s,%s" % (
            self.id,
            self.sourceid,
        )

    def to_dict(self):
        return {
            "id": self.id,
            "userid": self.userid,
            "contractsid": self.contractsid,
            "message": self.message,
            "updated_at": self.updated_at,
            "created_at": self.created_at,
            "sourcetype": self.sourcetype,
            "sourceid": self.sourceid,
        }

    @classmethod
    def get_by_id(cls, id):
        return cls.query.filter(cls.id == id).one()

    @classmethod
    def get_by_contractsid(cls,contractsid):
        return cls.query.filter(cls.contractsid == contractsid)

    @classmethod
    def get_by_sourceid(cls,sourcetype,sourceid):
        return cls.query.filter(cls.sourceid == sourceid,cls.sourcetype == sourcetype).first()

################模板20200626###############################################
@generic_repr("id", "code", "name","type","org_id","template")
class Templates(TimestampMixin, BelongsToOrgMixin, db.Model):
    id = Column(db.Integer, primary_key=True)
    org_id = Column(key_type("Organization"), db.ForeignKey("organizations.id"))
    org = db.relationship("Organization", back_populates="templates")
    userid = Column(db.Integer) #--操作员ID
    code = Column(db.String(100)) #编号
    name = Column(db.String(255)) #名称
    orgunit_id = Column(db.Integer, nullable=True,default=0) #--部门
    # type = Column(db.String(255))  # 合同类别
    type = Column(
        MutableList.as_mutable(postgresql.ARRAY(db.String(255))), nullable=True
    ) #类别
    template = Column(db.Text)  #  纪录事项
    is_active = Column(db.Boolean(True))
    __tablename__ = "templates"
    __table_args__ = (db.Index("templates_org_code_type","org_id","code","type","orgunit_id",  unique=True),)

    def __str__(self):
        return "%s,%s" % (
            self.code,
            self.name,
        )

    def to_dict(self):
        return {
            "id": self.id,
            "userid": self.userid,
            "code": self.code,
            "name": self.name,
            "type": self.type,
            "orgunit_id": self.orgunit_id,
            "template": self.template,
            "is_active": self.is_active,
            "updated_at": self.updated_at,
            "created_at": self.created_at,
        }

    @classmethod
    def get_by_id(cls, id):
        return cls.query.filter(cls.id == id).one()

    @classmethod
    def get_id(cls):
        strsql="""select array_to_json(array_agg(row_to_json(t))) from (select nextval('templates_id_seq'::regclass) as id) t """
        return db.session.execute(strsql).fetchone()
################################################
################设置20200914###############################################
@generic_repr("id", "code", "type")
class GlobalSetting(TimestampMixin, BelongsToOrgMixin, db.Model):
    id = Column(db.Integer, primary_key=True)
    org_id = Column(key_type("Organization"), db.ForeignKey("organizations.id"))
    org = db.relationship("Organization", back_populates="globalsetting")
    userid = Column(db.Integer) #--操作员ID
    type = Column(db.String(100)) #--模块
    code = Column(db.String(100)) #KEY
    value = Column(db.String(100)) #值
    message = Column(db.Text, default=None, nullable=True)  #  说明

    __tablename__ = "globalsetting"
    __table_args__ = (db.Index("globalsetting_org_type_code","org_id","code", "type", unique=True),)

    def __str__(self):
        return "%s,%s" % (
            self.code,
            self.type,
        )

    def to_dict(self):
        return {
            "id": self.id,
            "userid": self.userid,
            "type": self.type,
            "code": self.code,
            "value": self.value,
            "message": self.message,
            "updated_at": self.updated_at,
            "created_at": self.created_at,
        }

    @classmethod
    def get_by_id(cls, id):
        return cls.query.filter(cls.id == id).one()
################合同中止单20200524###############################################
@generic_repr("id", "code", "name")
class ContractSuspend(TimestampMixin, BelongsToOrgMixin, db.Model):
    id = Column(db.Integer, primary_key=True)
    org_id = Column(key_type("Organization"), db.ForeignKey("organizations.id"))
    org = db.relationship("Organization", back_populates="contractsuspend")
    userid = Column(db.Integer) #--操作员ID
    bill_date = Column(db.Date) #日期
    contractsid = Column(db.Integer) #--合同ID
    code = Column(db.String(100)) #编号
    name = Column(db.String(255)) #名称
    departmentid = Column(db.Integer, default=0) #--经办部门
    employeeid = Column(db.Integer, default=0) #--经办ren
    status =  Column(db.Integer, default=0, nullable=True) #--状态
    message = Column(db.Text, default=None, nullable=True)  #  纪录事项
    sourcetype = Column(db.String(255), default=None, nullable=True)  # 来源单据类型
    sourceid = Column(db.Integer, default=0, nullable=True) #--来源单据ID

    __tablename__ = "contractsuspend"
    __table_args__ = (db.Index("contractsuspend_org_code","org_id", "code", unique=True),)

    def __str__(self):
        return "%s,%s" % (
            self.code,
            self.name,
        )

    def to_dict(self):
        return {
            "id": self.id,
            "userid": self.userid,
            "bill_date": self.bill_date,
            "contractsid": self.contractsid,
            "code": self.code,
            "name": self.name,
            "departmentid": self.departmentid,
            "employeeid": self.employeeid,
            "status": self.status,
            "message": self.message,
            "updated_at": self.updated_at,
            "created_at": self.created_at,
            "sourcetype": self.sourcetype,
            "sourceid": self.sourceid,
        }

    @classmethod
    def get_by_id(cls, id):
        return cls.query.filter(cls.id == id).one()

    @classmethod
    def get_id(cls):
        strsql="""select array_to_json(array_agg(row_to_json(t))) from (select nextval('contractsuspend_id_seq'::regclass) as id) t """
        return db.session.execute(strsql).fetchone()
###############################################################